import plotly.io as pio
pio.renderers.default='notebook'
!pip install Pyppeteer
!pyppeteer-install
Defaulting to user installation because normal site-packages is not writeable
WARNING: The script pyppeteer-install.exe is installed in 'C:\Users\user\AppData\Roaming\Python\Python311\Scripts' which is not on PATH. Consider adding this directory to PATH or, if you prefer to suppress this warning, use --no-warn-script-location.
Collecting Pyppeteer Obtaining dependency information for Pyppeteer from https://files.pythonhosted.org/packages/3d/ee/fb2757a38025421fd3844a0ed0a230b78c9c04a66355024436cf3005a70c/pyppeteer-2.0.0-py3-none-any.whl.metadata Downloading pyppeteer-2.0.0-py3-none-any.whl.metadata (7.1 kB) Requirement already satisfied: appdirs<2.0.0,>=1.4.3 in c:\programdata\anaconda3\lib\site-packages (from Pyppeteer) (1.4.4) Requirement already satisfied: certifi>=2023 in c:\programdata\anaconda3\lib\site-packages (from Pyppeteer) (2023.7.22) Requirement already satisfied: importlib-metadata>=1.4 in c:\programdata\anaconda3\lib\site-packages (from Pyppeteer) (6.0.0) Collecting pyee<12.0.0,>=11.0.0 (from Pyppeteer) Obtaining dependency information for pyee<12.0.0,>=11.0.0 from https://files.pythonhosted.org/packages/16/cc/5cea8a0a0d3deb90b5a0d39ad1a6a1ccaa40a9ea86d793eb8a49d32a6ed0/pyee-11.1.0-py3-none-any.whl.metadata Downloading pyee-11.1.0-py3-none-any.whl.metadata (2.8 kB) Requirement already satisfied: tqdm<5.0.0,>=4.42.1 in c:\programdata\anaconda3\lib\site-packages (from Pyppeteer) (4.65.0) Requirement already satisfied: urllib3<2.0.0,>=1.25.8 in c:\programdata\anaconda3\lib\site-packages (from Pyppeteer) (1.26.16) Collecting websockets<11.0,>=10.0 (from Pyppeteer) Obtaining dependency information for websockets<11.0,>=10.0 from https://files.pythonhosted.org/packages/27/bb/6327e8c7d4dd7d5b450b409a461be278968ce05c54da13da581ac87661db/websockets-10.4-cp311-cp311-win_amd64.whl.metadata Downloading websockets-10.4-cp311-cp311-win_amd64.whl.metadata (6.4 kB) Requirement already satisfied: zipp>=0.5 in c:\programdata\anaconda3\lib\site-packages (from importlib-metadata>=1.4->Pyppeteer) (3.11.0) Requirement already satisfied: typing-extensions in c:\programdata\anaconda3\lib\site-packages (from pyee<12.0.0,>=11.0.0->Pyppeteer) (4.7.1) Requirement already satisfied: colorama in c:\programdata\anaconda3\lib\site-packages (from tqdm<5.0.0,>=4.42.1->Pyppeteer) (0.4.6) Downloading pyppeteer-2.0.0-py3-none-any.whl (82 kB) ---------------------------------------- 0.0/82.9 kB ? eta -:--:-- -------------- ------------------------- 30.7/82.9 kB 1.3 MB/s eta 0:00:01 -------------- ------------------------- 30.7/82.9 kB 1.3 MB/s eta 0:00:01 -------------- ------------------------- 30.7/82.9 kB 1.3 MB/s eta 0:00:01 ----------------------------- ---------- 61.4/82.9 kB 326.1 kB/s eta 0:00:01 ---------------------------------------- 82.9/82.9 kB 331.7 kB/s eta 0:00:00 Downloading pyee-11.1.0-py3-none-any.whl (15 kB) Downloading websockets-10.4-cp311-cp311-win_amd64.whl (101 kB) ---------------------------------------- 0.0/101.4 kB ? eta -:--:-- ---- ----------------------------------- 10.2/101.4 kB ? eta -:--:-- ------------------------ --------------- 61.4/101.4 kB 1.1 MB/s eta 0:00:01 -------------------------------------- 101.4/101.4 kB 829.1 kB/s eta 0:00:00 Installing collected packages: websockets, pyee, Pyppeteer Successfully installed Pyppeteer-2.0.0 pyee-11.1.0 websockets-10.4
'pyppeteer-install' is not recognized as an internal or external command, operable program or batch file.
import pyodbc
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import plotly.express as px
import plotly.graph_objects as go
cnxn_str =("Driver={ODBC Driver 17 for SQL Server};"
"Server=LAPTOP-PU7MK2SG\IMAN;"
"Database=imanportfolio2;"
"Trusted_Connection=yes;")
cnxn = pyodbc.connect(cnxn_str)
cursor = cnxn.cursor()
query = """
select *
from tsecurity_data..qty
order by Product
"""
qty_df = pd.read_sql_query(query, cnxn)
qty_df
C:\Users\user\AppData\Local\Temp\ipykernel_72\1367283235.py:9: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy. qty_df = pd.read_sql_query(query, cnxn)
| Product | Store_number | Jan23 | Feb23 | Mar23 | Apr23 | May23 | Jun23 | Jul23 | Aug23 | ... | Mar22 | Apr22 | May22 | Jun22 | Jul22 | Aug22 | Sep22 | Oct22 | Nov22 | Dec22 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | A | 1.0 | 0.0 | 0.0 | 4.0 | 0.0 | 5.0 | 0.0 | 5.0 | 0.0 | ... | 10.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
| 1 | A | 2.0 | 5.0 | 0.0 | 5.0 | 0.0 | 0.0 | 0.0 | 0.0 | 5.0 | ... | 5.0 | 5.0 | 0.0 | 6.0 | 5.0 | 0.0 | 2.0 | 5.0 | 0.0 | 0.0 |
| 2 | A | 3.0 | 5.0 | 10.0 | 50.0 | 10.0 | 30.0 | 10.0 | 30.0 | 15.0 | ... | 20.0 | 35.0 | 20.0 | 27.0 | 21.0 | 0.0 | 20.0 | 15.0 | 15.0 | 20.0 |
| 3 | A | 4.0 | 20.0 | 0.0 | 20.0 | 0.0 | 30.0 | 0.0 | 0.0 | 40.0 | ... | 10.0 | 20.0 | 10.0 | 20.0 | 20.0 | 0.0 | 20.0 | 20.0 | 0.0 | 10.0 |
| 4 | A | 5.0 | 0.0 | 20.0 | 0.0 | 25.0 | 20.0 | 10.0 | 0.0 | 20.0 | ... | 10.0 | 10.0 | 10.0 | 10.0 | 0.0 | 20.0 | 0.0 | 0.0 | 20.0 | 5.0 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 5388 | H | 1027.0 | 0.0 | 0.0 | 3.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
| 5389 | H | 1029.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
| 5390 | H | 1030.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
| 5391 | H | 1031.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 10.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
| 5392 | H | 1032.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 10.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
5393 rows × 23 columns
query = """
SELECT
Product,
SUM(Jan23 + Feb23 + Mar23) AS Q1_2023,
SUM(Apr23 + May23 + Jun23) AS Q2_2023,
SUM(Jul23 + Aug23 + Sep23) AS Q3_2023,
SUM(Jan22 + Feb22 + Mar22) AS Q1_2022,
SUM(Apr22 + May22 + Jun22) AS Q2_2022,
SUM(Jul22 + Aug22 + Sep22) AS Q3_2022,
SUM(Oct22 + Nov22 + Dec22) AS Q4_2022
FROM tsecurity_data..qty
GROUP BY Product
order by Product
"""
quarterly_product_qty_df = pd.read_sql_query(query, cnxn)
quarterly_product_qty_df
C:\Users\user\AppData\Local\Temp\ipykernel_72\2849456373.py:18: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
| Product | Q1_2023 | Q2_2023 | Q3_2023 | Q1_2022 | Q2_2022 | Q3_2022 | Q4_2022 | |
|---|---|---|---|---|---|---|---|---|
| 0 | A | 25207.0 | 39027.0 | 12027.0 | 12566.0 | 39633.0 | 21195.0 | 15865.0 |
| 1 | B | 27326.0 | 38924.0 | 8879.0 | 10670.0 | 39649.0 | 20792.0 | 13721.0 |
| 2 | C | 19787.0 | 28970.0 | 8792.0 | 9533.0 | 30461.0 | 15367.0 | 11265.0 |
| 3 | D | 12520.0 | 18750.0 | 4920.0 | 6145.0 | 19812.0 | 9570.0 | 7270.0 |
| 4 | E | 1557.0 | 2376.0 | 766.0 | 664.0 | 2119.0 | 1157.0 | 870.0 |
| 5 | F | 894.0 | 3703.0 | 3024.0 | 0.0 | 0.0 | 0.0 | 2632.0 |
| 6 | G | 3919.0 | 7422.0 | 6995.0 | 0.0 | 0.0 | 0.0 | 6011.0 |
| 7 | H | 469.0 | 1340.0 | 1433.0 | 0.0 | 0.0 | 0.0 | 1784.0 |
quarters = ['Q1_2023', 'Q2_2023', 'Q3_2023', 'Q1_2022', 'Q2_2022', 'Q3_2022', 'Q4_2022']
# Create traces for each quarter
traces = []
for quarter in quarters:
trace = go.Bar(
x=quarterly_product_qty_df['Product'],
y=quarterly_product_qty_df[quarter],
name=quarter
)
traces.append(trace)
# Define layout
layout = go.Layout(
title='Quarterly Quantity by Product',
xaxis=dict(title='Product'),
yaxis=dict(title='Quantity'),
barmode='group'
)
# Create figure
fig = go.Figure(data=traces, layout=layout)
# Show plot
fig.show()
query = """
select Product,
(sum(Jan23) + sum(Feb23) + sum(Mar23) + sum(Apr23) +
sum(May23)+ sum(Jun23) + sum(Jul23) + sum(Aug23) + sum(Sep23))total_qty_2023,
(sum(Jan22) + sum(Feb22) + sum(Mar22) + sum(Apr22) +
sum(May22)+ sum(Jun22) + sum(Jul22) + sum(Aug22) + sum(Sep22))total_qty_2022
from tsecurity_data..qty
group by Product
order by (sum(Jan23) + sum(Feb23) + sum(Mar23) + sum(Apr23) +
sum(May23)+ sum(Jun23) + sum(Jul23) + sum(Aug23) + sum(Sep23)) desc
"""
yearly_product_qty_df= pd.read_sql_query(query, cnxn)
yearly_product_qty_df
C:\Users\user\AppData\Local\Temp\ipykernel_72\3307530111.py:14: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
| Product | total_qty_2023 | total_qty_2022 | |
|---|---|---|---|
| 0 | A | 76261.0 | 73394.0 |
| 1 | B | 75129.0 | 71111.0 |
| 2 | C | 57549.0 | 55361.0 |
| 3 | D | 36190.0 | 35527.0 |
| 4 | G | 18336.0 | 0.0 |
| 5 | F | 7621.0 | 0.0 |
| 6 | E | 4699.0 | 3940.0 |
| 7 | H | 3242.0 | 0.0 |
products = yearly_product_qty_df['Product']
qty_2023 = yearly_product_qty_df['total_qty_2023']
qty_2022 = yearly_product_qty_df['total_qty_2022']
# Set the width of the bars
bar_width = 0.35
# Set the positions of the bars on the x-axis
r1 = np.arange(len(products))
r2 = [x + bar_width for x in r1]
# Create the bar plots
plt.bar(r1, qty_2023, color='skyblue', width=bar_width, edgecolor='grey', label='Quantity Sold in 2023')
plt.bar(r2, qty_2022, color='lightgreen', width=bar_width, edgecolor='grey', label='Quantity Sold in 2022')
# Add xticks on the middle of the group bars
plt.xlabel('Product', fontweight='bold')
plt.ylabel('Quantity Sold', fontweight='bold')
plt.xticks([r + bar_width/2 for r in range(len(products))], products)
# Add title and legend
plt.title('Quantity Sold in 2023 vs 2022')
plt.legend()
# Show plot
plt.show()
#yearly_qty_df['norm_total_qty_2023']= (yearly_qty_df['total_qty_2023']/3242)
#yearly_qty_df['norm_total_qty_2022']= (yearly_qty_df['total_qty_2022']/3940)
fig = go.Figure()
# Add bar trace for quantity sold in 2023
fig.add_trace(go.Bar(
x=yearly_product_qty_df['Product'],
y=yearly_product_qty_df['total_qty_2023'],
name='Quantity Sold in 2023'
))
# Add bar trace for quantity sold in 2022
fig.add_trace(go.Bar(
x=yearly_product_qty_df['Product'],
y=yearly_product_qty_df['total_qty_2022'],
name='Quantity Sold in 2022'
))
# Update layout
fig.update_layout(
title='Quantity Sold in 2023 vs 2022',
xaxis_title='Product',
yaxis_title='Quantity Sold',
barmode='group'
)
# Show plot
fig.show()
query= """
select
(sum(Jan23) + sum(Feb23) + sum(Mar23) + sum(Apr23) +
sum(May23)+ sum(Jun23) + sum(Jul23) + sum(Aug23) + sum(Sep23))total_qty_2023,
(sum(Jan22) + sum(Feb22) + sum(Mar22) + sum(Apr22) +
sum(May22)+ sum(Jun22) + sum(Jul22) + sum(Aug22) + sum(Sep22))total_qty_2022
from tsecurity_data..qty
order by (sum(Jan23) + sum(Feb23) + sum(Mar23) + sum(Apr23) +
sum(May23)+ sum(Jun23) + sum(Jul23) + sum(Aug23) + sum(Sep23)) desc
"""
yearly_qty_df = pd.read_sql_query(query, cnxn)
yearly_qty_df
C:\Users\user\AppData\Local\Temp\ipykernel_72\3933393213.py:13: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
| total_qty_2023 | total_qty_2022 | |
|---|---|---|
| 0 | 279027.0 | 239333.0 |
import pandas as pd
import plotly.graph_objects as go
total_qty_2023 = yearly_qty_df['total_qty_2023'].sum()
total_qty_2022 = yearly_qty_df['total_qty_2022'].sum()
fig = go.Figure(data=[go.Pie(labels=['Total Quantity Sold in 2023', 'Total Quantity Sold in 2022'],
values=[total_qty_2023, total_qty_2022])])
fig.update_layout(title='Total Quantity Sold in 2023 vs 2022')
fig.show()
query="""
select Product, count(Product)selling_frequency
from tsecurity_data..qty
group by Product
order by count(Product) desc
"""
product_count_df=pd.read_sql_query(query,cnxn)
product_count_df
C:\Users\user\AppData\Local\Temp\ipykernel_72\1722162897.py:8: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
| Product | selling_frequency | |
|---|---|---|
| 0 | A | 911 |
| 1 | B | 901 |
| 2 | C | 887 |
| 3 | D | 843 |
| 4 | G | 605 |
| 5 | F | 561 |
| 6 | H | 430 |
| 7 | E | 255 |
fig = go.Figure(data=[go.Bar(x=product_count_df['Product'],
y=product_count_df['selling_frequency'])])
fig.update_layout(
title='Product Selling Frequency',
xaxis_title ='Product',
yaxis_title ='Selling Frequency',
bargap=0.05)
fig.show()